/*
 * @(#)JDBCTools.java Time: 2013-1-11
 *
 * Copyright 2013 xuedou.com All rights reserved.
 */

package xuedou.skymoni.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

/**
 *<pre>类说明</pre>
 *<b>功能描述：</b>
 * 数据库工具
 * @author  meiguanghui meiguanghui@xuedou.com
 * @version 1.0, 2013-1-11
 */
public class JDBCUtil {
				
			private static Connection conn=null;
			private static PreparedStatement ps=null;
			private static ResultSet rs=null;
			
		    private InitialContext ctx = null;	
			private DataSource ds = null;   //数据源
			
			private static   int i=0;
			
			/**
			 * 建立连接
			 */
			private JDBCUtil() {
				try {
					//初始上下文
					ctx = new InitialContext();
					//数据源
					ds=(DataSource)ctx.lookup("java:comp/env/MyDatabaseSource");
					//从数据源里取链接
				    conn =ds.getConnection();	
				    
					//conn = DriverManager.getConnection(serverUrl, databaseUser,password);
					if(null!=conn) {
						Logger.getLogger(JDBCUtil.class).info("已经建立链接");
					}
				} catch (SQLException e) {
					e.printStackTrace();
					Logger.getLogger(JDBCUtil.class).warn(e.toString());
				} catch (NamingException e) {
					e.printStackTrace();
				}
			}
			
			/**
			 * 创建语句
			 * @param sqlStr mysql语句
			 * @param values 参数页表
			 */
			private static void setCreateSQL(String sqlStr,String[] values)
			{
				//3.创建语句
				try {
					ps=conn.prepareStatement(sqlStr);//批处理防止数据注入!!
					for(int j=0;j<values.length;j++)
					{
						ps.setString(j+1,values[j]);	 //系统过滤敏感危险字符
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					Logger.getLogger(JDBCUtil.class).warn(e.toString());
				}
			}
			
			/**
			 * 可以完成数据的创建,修改,删除
			 * @param sqlStr mysql语句
			 * @param values 参数页表
			 * @return 执行修改后的条目
			 */
			public static  int update(String sqlStr,String[] values)
			{
				setCreateSQL(sqlStr,values);
				try {
					i=ps.executeUpdate();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					Logger.getLogger(JDBCUtil.class).warn(e.toString());
				}
				return i;
			}
			
			/**
			 * 读取数据
			 * @param sqlStr mysql语句
			 * @param values 参数页表
			 * @return
			 */
			public static  ResultSet read(String sqlStr, String[] values)
			{
				setCreateSQL(sqlStr,values);
				 try {
					rs=ps.executeQuery();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					Logger.getLogger(JDBCUtil.class).warn(e.toString());
				}
				return rs;
			}
			
			/**
			 * 检测是否在数据库中已经存在,无值返回0
			 * @param sqlStr mysql语句
			 * @param values 参数页表
			 * @return 找到的条目
			 */
			public static int isHave(String sqlStr, String[] values)
			{
				int count=0;
				try 
				{
					rs = read(sqlStr, values);
					if(rs.next()){
						rs.last();
						count=rs.getRow();
					}
				} 
				catch (SQLException e) 
				{
					e.printStackTrace();
					Logger.getLogger(JDBCUtil.class).warn(e.toString());
				}
				return count;
			}
			
			/**
			 * 释放数据库资源
			 */
			public static  void free()
			{
							try {   if (rs != null) { rs.close(); } } 
							catch (SQLException e2) { 
								Logger.getLogger(JDBCUtil.class).warn(e2.toString());
								e2.printStackTrace();
								}
							
							/*--------finally2----------*/
							finally
							{
								try {   if (ps!= null) { ps.close();} } 
								catch (SQLException e3) { 
									Logger.getLogger(JDBCUtil.class).warn(e3.toString());
									e3.printStackTrace();
									}
								
								/*---finally3----*/
								finally
								{
								try { if (conn != null) { conn.close();} } 
								catch (SQLException e4) { 
									Logger.getLogger(JDBCUtil.class).warn(e4.toString());
									e4.printStackTrace();
									}
								}
								/*---finally3 End----*/
							
								
								try {
									if(conn.isClosed()){
										Logger.getLogger(JDBCUtil.class).info("已成功释放链接资源.");
									} else {
										Logger.getLogger(JDBCUtil.class).warn("资源没有释放!");
									}
								} catch (SQLException e) {
									e.printStackTrace();
									Logger.getLogger(JDBCUtil.class).warn(e.toString());
								}
								
							}
							/*--------finally2 End----------*/

			}
			
			/**
			 * 获取 JDBCUtil对象
			 * @return JDBCUtil
			 */
			public static JDBCUtil getJDBCUtil() {
				return new JDBCUtil();
			}
}
